package com.hongdee.crm.statistics.service.impl;

import com.hongdee.common.util.StringUtil;
import com.hongdee.common.util.TokenUtil;
import com.hongdee.crm.Constant;
import com.hongdee.crm.loss.dao.ITcrmLossDao;
import com.hongdee.crm.loss.service.TCrmLossService;
import com.hongdee.crm.statistics.service.ITCrmLossStatisticsService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 流失客户统计
 * Created by 王帅 on 2017/6/2.
 */
@Service
public class TCrmLossStatisticsServiceImpl implements ITCrmLossStatisticsService {

    @Autowired
    private ITcrmLossDao tcrmLossDao;

    @Autowired
    private TCrmLossService crmLossService;

    /**
     * 统计流失客户未进店原因分析
     *
     * @param uid
     * @param time
     * @return
     * @throws Exception
     */
    @Override
    public List<Map<String, Object>> lossCustomerNotInStoreStatistics(String uid, String time,String companyId,
                                                                      String area,String brand) throws Exception {
        //生成sql
        StringBuffer sqlstr = new StringBuffer();
        List<Object> params = new ArrayList<>();
        this.newFirstCreateLossStatisticsSql(sqlstr, params, uid, time,companyId,area,brand);
        return tcrmLossDao.list(sqlstr.toString(), params.toArray());
    }

    private void firstCreateLossStatisticsSql(StringBuffer sqlstr, List<Object> params, String uid, String time,
                                              String companyId,String area,String brand) throws Exception {
        //获得流失方向信息
        List<Map<String, Object>> lossAdd = crmLossService.getLossListByTypeCode("2", uid,companyId);
        sqlstr.append(" select * from (");
        this.createLossStatisticsSql(sqlstr, params, uid, time, lossAdd,companyId,area,brand);
        sqlstr.append(" )t ");
        sqlstr.append(" union all ");
        sqlstr.append(" select '合计', ");
        for (Map<String, Object> map : lossAdd) {
            String code = StringUtil.formatNull(map.get("code")).replaceAll(" ", "");
            sqlstr.append("SUM(`" + code + "`),");
        }
        sqlstr.append(" SUM(dhtj*1),SUM(rybf*1),SUM(wjt*1),SUM(khjf*1),SUM(lcwd*1),SUM(zjtm*1),SUM(yyyh*1),SUM(clzr*1)," +
                "SUM(rzwd*1),SUM(glc*1),SUM(chsy*1),SUM(fbr*1),SUM(qdc*1),SUM(wxzl*1),SUM(wxjg*1),SUM(wxxl*1),SUM(fwtd*1)," +
                "SUM(fwhj*1),SUM(lcy*1),SUM(qt*1),SUM(lcwdlv*1),SUM(jqdd*1),SUM(zjtmlv*1),SUM(yyyhlv*1),t2.company_id,t2.o_name ");
        sqlstr.append(" from (");
        this.createLossStatisticsSql(sqlstr, params, uid, time, lossAdd,companyId,area,brand);
        sqlstr.append(" )t2 ");
    }

    /**
     * 流失客户未进店原因分析
     *
     * @param sqlStr
     * @param params
     * @param uid
     * @param time
     * @throws Exception
     */
    private void createLossStatisticsSql(StringBuffer sqlStr, List<Object> params, String uid, String time,
                                         List<Map<String, Object>> lossAdd,String companyId,String area,String brand)
            throws Exception {

        sqlStr.append(" select tact.type_code, ");

        for (Map<String, Object> map : lossAdd) {
            String code = StringUtil.formatNull(map.get("code")).replaceAll(" ", "");
            sqlStr.append("COUNT( CASE WHEN  exd.maintain_adress  = '" + code + "' then 1 else NULL end) as '" + code + "',");
        }
        sqlStr.append(" COUNT( CASE WHEN  exd.fail_reason  = 'dhtj' then 1 else NULL end) as 'dhtj', " +
                " COUNT( CASE WHEN  exd.fail_reason  = 'rybf' then 1 else null end) as 'rybf', " +
                " COUNT( CASE WHEN  exd.fail_reason  = 'wjt' then 1 else null end) as 'wjt', " +
                " COUNT( CASE WHEN  exd.fail_reason  = 'khjf' then 1 else null end) as 'khjf', " +
                " COUNT( CASE WHEN  exd.fail_reason  = 'lcwd' then 1 else null end) as 'lcwd', " +
                " COUNT( CASE WHEN  exd.fail_reason  = 'zjtm' then 1 else null end) as 'zjtm', " +
                " COUNT( CASE WHEN  exd.fail_reason  = 'yyyh' then 1 else null end) as 'yyyh', " +
                " COUNT( CASE WHEN exd.lost_reason = 'clzr' then 1 else null end) as  'clzr',   " +
                " COUNT( CASE WHEN exd.lost_reason = 'rzwd' then 1 else null end) as  'rzwd',   " +
                " COUNT( CASE WHEN exd.lost_reason = 'glc' then 1 else null end) as  'glc',   " +
                " COUNT( CASE WHEN exd.lost_reason = 'chsy' then 1 else null end) as  'chsy',   " +
                " COUNT( CASE WHEN exd.lost_reason = 'fbr' then 1 else null end) as  'fbr',   " +
                " COUNT( CASE WHEN exd.lost_reason = 'qdc' then 1 else null end) as  'qdc',   " +
                " COUNT( CASE WHEN exd.lost_reason = 'wxzl' then 1 else null end) as  'wxzl',   " +
                " COUNT( CASE WHEN exd.lost_reason = 'wxjg' then 1 else null end) as  'wxjg',   " +
                " COUNT( CASE WHEN exd.lost_reason = 'wxxl' then 1 else null end) as  'wxxl',   " +
                " COUNT( CASE WHEN exd.lost_reason = 'fwtd' then 1 else null end) as  'fwtd',    " +
                " COUNT( CASE WHEN exd.lost_reason = 'fwhj' then 1 else null end) as  'fwhj',   " +
                " COUNT( CASE WHEN exd.lost_reason = 'lcy' then 1 else null end) as  'lcy',   " +
                " COUNT( CASE WHEN exd.lost_reason = 'qt' then 1 else null end) as  'qt', ");
        sqlStr.append(" COUNT( CASE WHEN  exd.intention_level  = 'lcwd' then 1 else NULL end) as 'lcwdlv', " +
                " COUNT( CASE WHEN  exd.intention_level  = 'jqdd' then 1 else NULL end) as 'jqdd', " +
                " COUNT( CASE WHEN  exd.intention_level  = 'zjtm' then 1 else NULL end) as 'zjtmlv', " +
                " COUNT( CASE WHEN  exd.intention_level  = 'yyyh' then 1 else NULL end) as 'yyyhlv',tact.company_id,tact.o_name ");
        sqlStr.append(" from ( " +
                " select tact.*,task.type_code,task.type_pcode,task.status taskStatus,task.is_back,task.o_name from ");
        sqlStr.append(" (select task.*,bc.o_name from t_crm_task_real task, ");
        sqlStr.append(" (select * from t_bc_organ  where 1=1  ");

        Constant.companyAndAreaAndBrand(uid,companyId,area,brand,sqlStr,params,"");
        sqlStr.append(" ) bc where task.company_id=bc.id and task.type_pcode='ls'");
        if (!"".equals(time)) {
            sqlStr.append(" and DATE_FORMAT(task.expect_date,'%Y-%m')=?  ");
            params.add(time);
        }
        if (!TokenUtil.isCompanyAdmin(uid)) {
            sqlStr.append(" and task.sa_id=? ");
            params.add(uid);
        }
        sqlStr.append(")task,");
        sqlStr.append(" t_crm_car_contact tact " +
                " where task.id=tact.task_id  and task.company_id=tact.company_id");
        sqlStr.append(" )tact, " +
                " t_crm_car_contact_extend exd " +
                " where tact.id=exd.c_id group by tact.type_code order by tact.type_code*1");

    }

    /**
     * 流失月报分析：侯念晨
     * @param sqlstr
     * @param params
     * @param uid
     * @param time
     * @param companyId
     * @param area
     * @param brand
     * @throws Exception
     */

    private void newFirstCreateLossStatisticsSql(StringBuffer sqlstr, List<Object> params, String uid, String time,
                                                 String companyId,String area,String brand) throws Exception {
        //获得流失方向信息
        List<Map<String, Object>> lossAdd = crmLossService.getLossListByTypeCode("2", uid,companyId);
        sqlstr.append(" select * from (");
        this.newCreateLossStatisticsSql(sqlstr, params, uid, time, lossAdd,companyId,area,brand);
        sqlstr.append(" )t ");
        sqlstr.append(" union all ");
        sqlstr.append(" select '合计', ");
        for (Map<String, Object> map : lossAdd) {
            String code = StringUtil.formatNull(map.get("code")).replaceAll(" ", "");
            sqlstr.append("SUM(`" + code + "`),");
        }
        sqlstr.append(" SUM(kshch*1),SUM(tj*1),SUM(wrjt*1),SUM(rybf*1),SUM(khjf*1),SUM(clzrhbf*1),SUM(rzwd*1),SUM(glc*1)," +
                "SUM(chsy*1),SUM(fczbr*1),SUM(wdc*1),SUM(wxzl*1),SUM(wxjg*1),SUM(wxxl*1),SUM(fwtd*1),SUM(fwhj*1),SUM(jly*1)," +
                "SUM(qt*1),SUM(lcwd*1),SUM(sjwd*1),SUM(jqtm*1),t2.company_id,t2.o_name ");
        sqlstr.append(" from (");
        this.newCreateLossStatisticsSql(sqlstr, params, uid, time, lossAdd,companyId,area,brand);
        sqlstr.append(" )t2 ");
    }

    /**
     * 流失客户未进店原因分析
     * 侯念晨
     *
     * @param sqlStr
     * @param params
     * @param uid
     * @param time
     * @throws Exception
     */
    private void newCreateLossStatisticsSql(StringBuffer sqlStr, List<Object> params, String uid, String time,
                                            List<Map<String, Object>> lossAdd,String companyId,String area,String brand)
            throws Exception {

        sqlStr.append(" select tact.type_code, ");

        for (Map<String, Object> map : lossAdd) {
            String code = StringUtil.formatNull(map.get("code")).replaceAll(" ", "");
            sqlStr.append("COUNT( CASE WHEN  res.result_d  = '" + code + "' then 1 else NULL end) as '" + code + "',");
        }
        sqlStr.append(" COUNT( CASE WHEN  res.result_b  = '空号或错号' then 1 else NULL end) as 'kshch', " +
                " COUNT( CASE WHEN  res.result_b  = '停机' then 1 else null end) as 'tj', " +
                " COUNT( CASE WHEN  res.result_b  = '无人接听' then 1 else null end) as 'wrjt', " +
                " COUNT( CASE WHEN  res.result_b  = '人员不符' then 1 else null end) as 'rybf', " +
                " COUNT( CASE WHEN  res.result_b  = '客户拒访' then 1 else null end) as 'khjf', " +
                " COUNT( CASE WHEN  res.result_c  = '车辆转让或报废' then 1 else null end) as 'clzrhbf', " +
                " COUNT( CASE WHEN  res.result_c  = '人在外地' then 1 else null end) as 'rzwd', " +
                " COUNT( CASE WHEN res.result_c = '过路车' then 1 else null end) as  'glc',   " +
                " COUNT( CASE WHEN res.result_c = '车很少用' then 1 else null end) as  'chsy',   " +
                " COUNT( CASE WHEN res.result_c = '非车主本人' then 1 else null end) as  'fczbr',   " +
                " COUNT( CASE WHEN res.result_c = '外地车' then 1 else null end) as  'wdc',   " +
                " COUNT( CASE WHEN res.result_c = '维修质量' then 1 else null end) as  'wxzl',   " +
                " COUNT( CASE WHEN res.result_c = '维修价格' then 1 else null end) as  'wxjg',   " +
                " COUNT( CASE WHEN res.result_c = '维修效率' then 1 else null end) as  'wxxl',   " +
                " COUNT( CASE WHEN res.result_c = '服务态度' then 1 else null end) as  'fwtd',   " +
                " COUNT( CASE WHEN res.result_c = '服务环境' then 1 else null end) as  'fwhj',   " +
                " COUNT( CASE WHEN res.result_c = '距离远' then 1 else null end) as  'jly',    " +
                " COUNT( CASE WHEN res.result_c = '其它' then 1 else null end) as  'qt',   " +
                " COUNT( CASE WHEN res.result_c = '里程未到' then 1 else null end) as  'lcwd',   " +
                " COUNT( CASE WHEN res.result_c = '时间未到' then 1 else null end) as  'sjwd', " +
                " COUNT( CASE WHEN res.result_c = '近期太忙' then 1 else null end) as  'jqtm', ");
        sqlStr.append(" tact.company_id,tact.o_name ");
        sqlStr.append(" from ( " +
                " select tact.*,task.type_code,task.type_pcode,task.status taskStatus,task.is_back,task.o_name from ");
        sqlStr.append(" (select task.*,bc.o_name from t_crm_task_real task, ");
        sqlStr.append(" (select * from t_bc_organ  where 1=1  ");

        Constant.companyAndAreaAndBrand(uid,companyId,area,brand,sqlStr,params,"");
        sqlStr.append(" ) bc where task.company_id=bc.id and task.type_pcode='ls'");
        if (!"".equals(time)) {
            sqlStr.append(" and DATE_FORMAT(task.expect_date,'%Y-%m')=?  ");
            params.add(time);
        }
        if (!TokenUtil.isCompanyAdmin(uid)) {
            sqlStr.append(" and task.sa_id=? ");
            params.add(uid);
        }
        sqlStr.append(")task,");
        sqlStr.append(" t_crm_car_contact tact " +
                " where task.id=tact.task_id  and task.company_id=tact.company_id");
        sqlStr.append(" )tact, " +
                " t_crm_car_contact_result res " +
                " where tact.id=res.c_id group by tact.type_code order by tact.type_code*1");

    }

    @Override
    public List<Map<String, Object>> lossCustomerOutsideMaintenStatistics(String uid, String time,String companyId,
                                                                          String area,String brand) throws Exception {
        //生成sql
        StringBuffer sqlstr = new StringBuffer();
        List<Object> params = new ArrayList<>();
        this.newCreateLossOutsideMaintenstatisticsSql(sqlstr, params, uid, time,companyId,area,brand);
        return tcrmLossDao.list(sqlstr.toString(), params.toArray());
    }


    private void createLossOutsideMaintenstatisticsSql(StringBuffer sqlStr, List<Object> params, String uid,
                                                       String time,String companyId,String area,String brand) throws Exception {
        //获得流失方向信息
        List<Map<String, Object>> lossAdd = crmLossService.getLossListByTypeCode("2", uid,companyId);
        sqlStr.append("select  ");
        for (Map<String, Object> map : lossAdd) {
            String code = StringUtil.formatNull(map.get("code"));
            sqlStr.append("COUNT( CASE WHEN  exd.maintain_adress  = '" + code + "' then 1 else NULL end) as '" + code + "',");
        }
        sqlStr.append(" CASE  " +
                " WHEN exd.lost_reason = 'clzr' THEN  " +
                "  '车辆转让或报废'  " +
                " WHEN exd.lost_reason = 'rzwd' THEN  " +
                "  '人在外地没时间保养'  " +
                " WHEN exd.lost_reason = 'glc' THEN  " +
                "  '过路车'  " +
                " WHEN exd.lost_reason = 'chsy' THEN  " +
                "  '车很少用'  " +
                " WHEN exd.lost_reason = 'fbr' THEN  " +
                "  '非车主本人接听电话'  " +
                " WHEN exd.lost_reason = 'qdc' THEN  " +
                "  '外地车'  " +
                " WHEN exd.lost_reason = 'wxzl' THEN  " +
                "  '维修质量'  " +
                " WHEN exd.lost_reason = 'wxjg' THEN  " +
                "  '维修价格'  " +
                " WHEN exd.lost_reason = 'wxxl' THEN  " +
                "  '维修效率'  " +
                " WHEN exd.lost_reason = 'fwtd' THEN  " +
                "  '服务态度'  " +
                " WHEN exd.lost_reason = 'fwhj' THEN  " +
                "  '服务环境'  " +
                " WHEN exd.lost_reason = 'lcy' THEN  " +
                "  '路程远'  " +
                " WHEN exd.lost_reason = 'qt' THEN  " +
                "  '其他'  " +
                " END AS lost_reason,bc.o_name,exd.company_id  " +
                " from (select exd.* from t_crm_car_contact_extend exd, " +
                "t_crm_car_contact contact " +
                " where exd.c_id=contact.id and  exd.lost_reason <>''");
        if (!TokenUtil.isCompanyAdmin(uid)) {
            sqlStr.append(" and contact.sa_id=? ");
            params.add(uid);
        }
        if (!"".equals(time)) {
            sqlStr.append(" and DATE_FORMAT(FROM_UNIXTIME(exd.create_time/1000),'%Y-%m')=? ");
            params.add(time);
        }
        sqlStr.append(" ) exd,");
        sqlStr.append(" (select * from t_bc_organ  where 1=1  ");

        Constant.companyAndAreaAndBrand(uid,companyId,area,brand,sqlStr,params,"");
        sqlStr.append(" ) bc where exd.company_id=bc.id ");

        sqlStr.append(" group by exd.lost_reason");

    }

    private void newCreateLossOutsideMaintenstatisticsSql(StringBuffer sqlStr, List<Object> params, String uid,
                                                       String time,String companyId,String area,String brand) throws Exception {
        //获得流失方向信息
        sqlStr.append("SELECT COUNT(*) num, res.result_c, task.o_name FROM t_crm_car_contact_result res, t_crm_car_contact tact, " +
                "( SELECT task.*, bc.o_name FROM t_crm_task_real task, " +
                "( SELECT * FROM t_bc_organ WHERE id = ? ) bc ");
        params.add(companyId);
        sqlStr.append("WHERE task.company_id = bc.id AND task.type_pcode = 'ls' ) task WHERE tact.task_id = task.id " +
                "AND DATE_FORMAT(task.expect_date, '%Y-%m') = ? ");
        params.add(time);
        sqlStr.append("AND res.c_id = tact.id AND res.result_a = '接通' AND res.result_b = '战败' " +
                "AND res.task_pcode = 'ls' GROUP BY res.result_c");
    }

    @Override
    public List<Map<String, Object>> lossPlace(String uid, String time,String companyId,
                                                                          String area,String brand) throws Exception {
        //生成sql
        StringBuffer sqlstr = new StringBuffer();
        List<Object> params = new ArrayList<>();
        this.newCreateLossPlaceSql(sqlstr, params, uid, time,companyId,area,brand);
        return tcrmLossDao.list(sqlstr.toString(), params.toArray());
    }

    private void newCreateLossPlaceSql(StringBuffer sqlStr, List<Object> params, String uid,
                                                          String time,String companyId,String area,String brand) throws Exception {
        sqlStr.append("SELECT COUNT(*) num, IF ( res.result_d = '' || res.result_d IS NULL, '未知', res.result_d ) result_d, " +
                "task.o_name FROM t_crm_car_contact_result res, t_crm_car_contact tact, " +
                "( SELECT task.*, bc.o_name FROM t_crm_task_real task, " +
                "( SELECT * FROM t_bc_organ WHERE id = ? ) bc ");
        params.add(companyId);
        sqlStr.append("WHERE task.company_id = bc.id AND task.type_pcode = 'ls' ) task " +
                "WHERE tact.task_id = task.id AND DATE_FORMAT(task.expect_date, '%Y-%m') = ?");
        params.add(time);
        sqlStr.append("AND res.c_id = tact.id AND res.result_a = '接通' " +
                "AND res.result_b = '战败' " +
                "AND res.task_pcode = 'ls' " +
                "GROUP BY res.result_d");
    }


}
